Outbound_Process

 
Documentation generated by Matillion ETL

Job: MDM21 Sales Rep Affiliation Orchestration

The data will come from Sales IQ


Correct MDM LOV LOAD

MDM21 - Daily-MDM21 Sales Rep Affiliation

Run Transformation
Parameter
Value
Transformation Job
Daily-MDM21 Sales Rep Affiliation
Set Scalar Variables
Set Grid Variables

Daily-MDM21 Sales Rep Affiliation - Post Prior Sales Rep Affil 0

Run Transformation
Parameter
Value
Transformation Job
Daily-MDM21 Sales Rep Affiliation - Post Prior Sales Rep Affil
Set Scalar Variables
Set Grid Variables

Unload: DL-FULL LOAD ETH MDM21 Sales Rep Affiliation

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_eth_mdm21_sales_rep_affiliation
S3 URL Location
${MDM21_S3_Target_Folder}
S3 Object Prefix
ETH MDM21 Sales Rep Affiliation.txt
IAM Role Arn
arn:aws:iam::<aws-account-id>:role/<role-name>
Generate Manifest
No
Data File Type
Delimited
Delimiter
\t
Compress Data
No
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
999
Include Header
No
Encryption
None
UNLOAD ('SELECT * FROM "jnjobprod"."outbound_dest_eth_mdm21_sales_rep_affiliation"') TO 's3://ethicon/outbound/Outbound_Process/MDM21/ETH MDM21 Sales Rep Affiliation.txt' ACCESS_KEY_ID 'XXXXXX' SECRET_ACCESS_KEY 'XXXXXX' PARALLEL OFF ALLOWOVERWRITE DELIMITER AS '\t' MAXFILESIZE 999 MB REGION AS 'us-west-2'

Set Current Table File For Archive (1)

Python Script
Parameter
Value
Script
context.updateVariable('Archive_temp_current_table', 'outbound_dest_eth_mdm21_sales_rep_affiliation')
context.updateVariable('Archive_temp_current_file', 'ETH MDM21 Sales Rep Affiliation.txt')
context.updateVariable('Archive_type','MDM21')
Interpreter
Python 3
Timeout
360

Archive Outbound Global Function 0

Run Orchestration
Parameter
Value
Orchestration Job
Archive Outbound Global Function
Set Scalar Variables
Set Grid Variables

Param: MDM LOV

Create Table
Parameter
Value
Schema
${Schema_Default}
New Table Name
MDM_LOV
Create/Replace
Replace
Table Metadata
ALIGNMENT STRUCTURE CODE, Text, 255, None, True, FRANCHISE CODE, Text, 255, None, True, ORG ID PREFIX, Text, 255, None, True, SALES ORG DIVISION CODE, Text, 255, None, True, SALES ORG NAME, Text, 255, None, True, DEFAULT BILL-TO, Text, 255, None, True, DEFAULT PAY-FROM, Text, 255, None, True, UPDATED BY, Text, 255, None, True, UPDATED DATE, date, 255, None, True
Distribution Style
Auto
Sort Key
Primary Key
Identity Columns
Backup Table
Yes

MDM LOV Load

S3 Load
Parameter
Value
Schema
${Schema_Default}
Target Table Name
mdm_lov
Load Columns
alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date
S3 URL Location
s3://ethicon/outbound/One MD Testing /Input files/
S3 Object Prefix
MDM_LOV_Param.txt
IAM Role ARN
arn:aws:iam::<aws-account-id>:role/<role-name>
Data File Type
Delimited
Delimiter
|
Explicit IDs
No
S3 Bucket Region
None
Compression Method
None
Encoding
UTF8
Remove Quotes
No
Replace Invalid Characters
?
Maximum Errors
0
Date Format
auto
Time Format
auto
Ignore Header Rows
1
Accept Any Date
Yes
Ignore Blank Lines
Yes
Truncate Columns
No
Fill Record
Yes
Trim Blanks
Yes
NULL As
\\N
Empty As Null
Yes
Blanks As Null
Yes
Comp Update
On
Stat Update
On
Escape
No
Round Decimals
Yes
Manifest
No

Job: Daily-MDM21 Sales Rep Affiliation

Participant-Geo Association - Get Fiscal Start Date and End Date
Team-Geo Association - Get Fiscal Start and End Date

TODO - Fix Input table name
Participant Geo Association
- Process Territories
- Process Division
Merge: Territory, Division
Get Latest Geography Information
Process: MDM LOV Parameters
Post Data to Output table
- Daily - ETH MDM21 Sales Rep Affiliation

- DL Part ID in Sales Rep Affiliation
Process MDM Prior Sales Rep Affiliation: Holding Territories not in Org file until the Friday processing.
Holding territories not in Org file until the Friday run

Param: Geo Hierarchy Structure

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_param_geo_hierarchy_structure
Column Names
upper_algn_struc_cd, lower_algn_struc_cd, efftv_start_dt, efftv_end_dt, level_cd
Trim Columns
No

Param: Geo Hire Filter Territory

Filter
Parameter
Value
Filter Conditions
level_cd, Is, Ilike, Territory
Combine Conditions
AND

Part-Geo: Expand by Upper Geo

Join
Parameter
Value
Main Table
Part-Geo: Filter Territory
Main Table Alias
pgft
Joins
Param: Geo Hire Filter Territory, ghft, Left
Join Expressions
"pgft"."algn_struc_cd" = "ghft"."upper_algn_struc_cd"
AND cast("pgft"."efftv_start_dt" as date) <= cast("ghft"."efftv_end_dt" as date)
AND cast("pgft"."efftv_end_dt" as date) >= cast("ghft"."efftv_start_dt" as date), pgft_Left_ghft
Output Columns
pgft.algn_struc_cd, algn_struc_cd, pgft.participant_id, participant_id, pgft.geo_id, geo_id, pgft.core_temp_cd, core_temp_cd, pgft.split_pct, split_pct, pgft.last_updated_dt, last_updated_dt, pgft.efftv_start_dt, efftv_start_dt, pgft.efftv_end_dt, efftv_end_dt, ghft.lower_algn_struc_cd, lower_algn_struc_cd, ghft.efftv_start_dt, new_efftv_start_dt, ghft.efftv_end_dt, new_efftv_end_dt

Filter: Upper Geo Not Null

Filter
Parameter
Value
Filter Conditions
lower_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Part-Geo: Merge Terr, Div

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
Yes
Remove duplicates
Yes

Part-Geo: Get Upper Last Updated

Join
Parameter
Value
Main Table
Part-Geo: Merge Terr, Div
Main Table Alias
pgf
Joins
Param: Alignment Structure, pas, Left
Join Expressions
"pgf"."lower_algn_struc_cd" ="pas"."algn_struc_cd", pgf_Left_pas
Output Columns
pgf.participant_id, participant_id, pgf.algn_struc_cd, pgf_algn_struc_cd, pgf.geo_id, geo_id, pgf.core_temp_cd, core_temp_cd, pgf.split_pct, split_pct, pgf.last_updated_dt, pgf_last_updated_dt, pgf.efftv_start_dt, pgf_efftv_start_dt, pgf.efftv_end_dt, pgf_efftv_end_dt, pgf.lower_algn_struc_cd, lower_algn_struc_cd, pgf.new_efftv_start_dt, new_efftv_start_dt, pgf.new_efftv_end_dt, new_efftv_end_dt, pgf.source_table, source_table, pas.last_updated_dt, pas_last_updated_dt

Part-Geo: Get Dates for Upper

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
GREATEST (cast("pgf_efftv_start_dt" as date), cast("new_efftv_start_dt" as date)), efftv_start_dt, LEAST (cast("pgf_efftv_end_dt" as date), cast("new_efftv_end_dt" as date)), efftv_end_dt, GREATEST (cast("pgf_last_updated_dt" as date), cast("pas_last_updated_dt" as date))

, last_updated_dt

Part-Geo : Manager

Rename
Parameter
Value
Column Mapping
participant_id, participant_id, lower_algn_struc_cd, algn_struc_cd, geo_id, geo_id, core_temp_cd, core_temp_cd, split_pct, split_pct, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, last_updated_dt, last_updated_dt

Part-Geo: Merge Terr - Mgr

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
Yes
Remove duplicates
No

Get Sales Rep Teams

Join
Parameter
Value
Main Table
Part-Geo: Merge Terr - Mgr
Main Table Alias
pgtm
Joins
Team-Geo: Adjust Dates, tg, Left
Join Expressions
"pgtm"."algn_struc_cd" = "tg"."algn_struc_cd"
AND "pgtm"."geo_id" = "tg"."geo_id"
AND Cast("pgtm"."efftv_start_dt" as date) <= Cast("tg"."tg_efftv_end_dt" as date)
AND Cast("pgtm"."efftv_end_dt" as date) >= Cast("tg"."tg_efftv_start_dt" as date), pgtm_Left_tg
Output Columns
pgtm.participant_id, participant_id, pgtm.algn_struc_cd, algn_struc_cd, pgtm.geo_id, geo_id, pgtm.core_temp_cd, core_temp_cd, pgtm.split_pct, split_pct, pgtm.efftv_start_dt, efftv_start_dt, pgtm.efftv_end_dt, efftv_end_dt, pgtm.last_updated_dt, last_updated_dt, tg.tg_efftv_start_dt, efftv_start_dt1, tg.tg_efftv_end_dt, efftv_end_dt1, tg.team_id, team_id, tg.split_pct, tg_split_pct

Resolve Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
GREATEST (cast("efftv_start_dt" as date), cast("efftv_start_dt1" as date)), efftv_start_dt, LEAST (cast("efftv_end_dt" as date), cast("efftv_end_dt1" as date)), efftv_end_dt, CASE WHEN "team_id" IS NULL OR "team_id" = '' THEN
"geo_id"
ELSE
"team_id"
END, team_id, Cast("split_pct" as decimal(10,4))
, split_pct

Bring in LOV Values

Join
Parameter
Value
Main Table
Resolve Dates
Main Table Alias
rd
Joins
MDM LOV Parameters, lov, Inner
Join Expressions
"rd"."algn_struc_cd" = "lov"."alignment structure code", rd_Inner_lov
Output Columns
rd.participant_id, participant_id, rd.algn_struc_cd, algn_struc_cd, rd.team_id, team_id, rd.core_temp_cd, core_temp_cd, rd.geo_id, geo_id, rd.split_pct, split_pct, rd.efftv_start_dt, efftv_start_dt, rd.efftv_end_dt, efftv_end_dt, rd.efftv_start_dt1, efftv_start_dt1, rd.efftv_end_dt1, efftv_end_dt1, rd.last_updated_dt, last_updated_dt, lov.org id prefix, org id prefix, lov.default bill-to, default bill-to, lov.default pay-from, default pay-from

Filter New Territories

Join
Parameter
Value
Main Table
Bring in LOV Values
Main Table Alias
lov
Joins
MDM Prior Org (2), mdmpo, Inner
Join Expressions
"lov"."algn_struc_cd" = "mdmpo"."algn_struc_cd"
AND "lov"."team_id" = "mdmpo"."geo_id", lov_Inner_mdmpo
Output Columns
lov.participant_id, participant_id, lov.algn_struc_cd, algn_struc_cd, lov.geo_id, geo_id, lov.core_temp_cd, core_temp_cd, lov.team_id, team_id, lov.split_pct, split_pct, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov.org id prefix, org id prefix, lov.default bill-to, default bill-to, lov.default pay-from, default pay-from, mdmpo.geo_id, dlpo_geo_id, mdmpo.algn_struc_cd, dlpo_algn_struc_cd

Exclude Future Dated

Join
Parameter
Value
Main Table
Filter New Territories
Main Table Alias
fnt
Joins
Identify Future Dated Records - Filter Yes, ifdr, Left
Join Expressions
"fnt"."algn_struc_cd" = "ifdr"."algn_struc_cd"
AND "fnt"."participant_id" = "ifdr"."participant_id"
AND "fnt"."team_id" = "ifdr"."team_id"
AND "fnt"."efftv_start_dt" = "ifdr"."efftv_start_dt"
AND "fnt"."efftv_end_dt" = "ifdr"."efftv_end_dt", fnt_Left_ifdr
Output Columns
fnt.participant_id, participant_id, fnt.algn_struc_cd, algn_struc_cd, fnt.geo_id, geo_id, fnt.core_temp_cd, core_temp_cd, fnt.team_id, team_id, fnt.split_pct, split_pct, fnt.efftv_start_dt, efftv_start_dt, fnt.efftv_end_dt, efftv_end_dt, fnt.last_updated_dt, last_updated_dt, fnt.org id prefix, org id prefix, fnt.default bill-to, default bill-to, fnt.default pay-from, default pay-from, ifdr.participant_id, ifdf_participant_id

Exclude Future Dated (Incl/Excl)

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "ifdf_participant_id" = '' or "ifdf_participant_id" is null THEN
'Excluded'
ELSE
'Included'
END, ax_flag

Exclude Future Dated - Included

Filter
Parameter
Value
Filter Conditions
ax_flag, Is, Ilike, Included
Combine Conditions
AND

Get Original for Future Dated

Join
Parameter
Value
Main Table
Exclude Future Dated - Included
Main Table Alias
gofd
Joins
Input: MDM Prior Sale Rep Affiliation, mdmpsra, Inner
Join Expressions
"gofd"."algn_struc_cd" = "mdmpsra"."algn_struc_cd"
AND "gofd"."geo_id" = "mdmpsra"."geo_id"
AND "gofd"."efftv_start_dt" = "mdmpsra"."geo_efftv_start_dt"
AND "gofd"."efftv_end_dt" = "mdmpsra"."geo_efftv_end_dt", gofd_Inner_mdmpsra
Output Columns
gofd.participant_id, participant_id, gofd.algn_struc_cd, algn_struc_cd, gofd.geo_id, geo_id, gofd.team_id, team_id, gofd.core_temp_cd, core_temp_cd, gofd.efftv_start_dt, efftv_start_dt, gofd.efftv_end_dt, efftv_end_dt, gofd.last_updated_dt, last_updated_dt, gofd.org id prefix, org id prefix, gofd.default bill-to, default bill-to, gofd.default pay-from, default pay-from

Combine Non Future and Original

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Remove duplicates

Distinct
Parameter
Value
Columns
participant_id, algn_struc_cd, geo_id, team_id, core_temp_cd, efftv_start_dt, efftv_end_dt, org id prefix, default bill-to, default pay-from

Post to MDM Prior Sale Rep Affiliation

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
stg_mdm_prior_sale_rep_affiliation
Fix Data Type Mismatches
No
Column Mapping
participant_id, participant_id, algn_struc_cd, algn_struc_cd, efftv_start_dt, geo_efftv_start_dt, efftv_end_dt, geo_efftv_end_dt, team_id, team_id, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from
Truncate
Truncate
Automatic Compression
No

Last Upt

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
sysdate, Last_Updated

stg_mdm_prior_sale_rep_affiliation_bkp

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
stg_mdm_prior_sale_rep_affiliation_bkp
Fix Data Type Mismatches
No
Column Mapping
participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_id, geo_id, team_id, team_id, core_temp_cd, core_temp_cd, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from, last_updated, last_updated
Truncate
Append

Exclude Future Dated: Excluded

Filter
Parameter
Value
Filter Conditions
ax_flag, is, Ilike, Excluded
Combine Conditions
AND

Excluded Not in Roster

Join
Parameter
Value
Main Table
Exclude Future Dated: Excluded
Main Table Alias
efdm
Joins
Filter Out Not In Sales Roster: Un-Matched, fonsrun, Left
Join Expressions
"efdm"."participant_id" = "fonsrun"."participant_id", efdm_Left_fonsrun
Output Columns
efdm.participant_id, participant_id, efdm.algn_struc_cd, algn_struc_cd, efdm.geo_id, geo_id, efdm.team_id, team_id, efdm.core_temp_cd, core_temp_cd, efdm.efftv_start_dt, efftv_start_dt, efdm.efftv_end_dt, efftv_end_dt, efdm.last_updated_dt, last_updated_dt, efdm.org id prefix, org id prefix, efdm.default bill-to, default bill-to, efdm.default pay-from, default pay-from, fonsrun.participant_id, fonsrun_participant_id

Excluded Not in Roster (Incl/Excl)

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "fonsrun_participant_id" = '' or "fonsrun_participant_id" is null THEN
'Excluded'
ELSE
'Included'
END, ax_flag

Excluded Not in Roster - Excluded

Filter
Parameter
Value
Filter Conditions
ax_flag, Is, Ilike, Excluded
Combine Conditions
AND

Excluded Not in Roster - Included

Filter
Parameter
Value
Filter Conditions
ax_flag, Is, Ilike, Included
Combine Conditions
AND

Get Original for Not in Sales

Join
Parameter
Value
Main Table
Excluded Not in Roster - Included
Main Table Alias
enrun
Joins
Input: MDM Prior Sale Rep Affiliation, psra, Inner
Join Expressions
"enrun"."algn_struc_cd" = "psra"."algn_struc_cd"
AND "enrun"."team_id" = "psra"."team_id"
AND cast("enrun"."efftv_start_dt" as date) = cast("psra"."geo_efftv_start_dt" as date)
AND "enrun"."participant_id" = "psra"."participant_id", enrun_Inner_psra
Output Columns
enrun.participant_id, participant_id, enrun.algn_struc_cd, algn_struc_cd, enrun.geo_id, geo_id, enrun.team_id, team_id, enrun.core_temp_cd, core_temp_cd, enrun.efftv_start_dt, efftv_start_dt, enrun.efftv_end_dt, efftv_end_dt, enrun.last_updated_dt, last_updated_dt, enrun.org id prefix, org id prefix, enrun.default bill-to, default bill-to, enrun.default pay-from, default pay-from, psra.participant_id, psra_participant_id

Find new/ changed/ deleted

Join
Parameter
Value
Main Table
Bring in LOV Values
Main Table Alias
lov
Joins
Input: MDM Prior Sale Rep Affiliation, psra, Left
Join Expressions
"lov"."algn_struc_cd" = "psra"."algn_struc_cd"
AND "lov"."participant_id" = "psra"."participant_id"
AND Cast("lov"."efftv_start_dt" as date) = Cast("psra"."geo_efftv_start_dt" as date)
AND Cast("lov"."efftv_end_dt" as date) = Cast("psra"."geo_efftv_end_dt" as date)
AND "lov"."team_id" = "psra"."team_id"
AND "lov"."org id prefix" = "psra"."org id prefix"
AND "lov"."default bill-to" = "psra"."default bill-to"
AND "lov"."default pay-from" = "psra"."default pay-from", lov_Left_psra
Output Columns
lov.participant_id, lov_participant_id, lov.algn_struc_cd, lov_algn_struc_cd, lov.geo_id, lov_geo_id, lov.team_id, lov_team_id, lov.core_temp_cd, lov_core_temp_cd, lov.split_pct, lov_split_pct, lov.efftv_start_dt, lov.efftv_start_dt, lov.efftv_end_dt, lov.efftv_end_dt, lov.efftv_start_dt1, efftv_start_dt1, lov.efftv_end_dt1, efftv_end_dt1, lov.last_updated_dt, lov.last_updated_dt, lov.org id prefix, lov_org id prefix, lov.default bill-to, lov_default bill-to, lov.default pay-from, lov_default pay-from, psra.participant_id, psra_participant_id, psra.algn_struc_cd, psra_algn_struc_cd, psra.geo_efftv_start_dt, psara_geo_efftv_start_dt, psra.geo_efftv_end_dt, psra_geo_efftv_end_dt, psra.geo_id, psra_geo_id, psra.org id prefix, psra_org id prefix, psra.default bill-to, psra_default bill-to, psra.default pay-from, psra_default pay-from

Find new/ changed/ deleted MATCHED

Filter
Parameter
Value
Filter Conditions
psra_algn_struc_cd, Not, Null
Combine Conditions
AND

Find new/ changed/ deleted UN-MATCHED RIGHT

Filter
Parameter
Value
Filter Conditions
lov_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Find new/ changed/ deleted UN-MATCHED -Column

Rename
Parameter
Value
Column Mapping
psra_participant_id, participant_id, psra_algn_struc_cd, algn_struc_cd, psara_geo_efftv_start_dt, geo_efftv_start_dt, psra_geo_efftv_end_dt, geo_efftv_end_dt, lov_team_id, team_id, lov_geo_id, geo_id, psra_org id prefix, org id prefix, psra_default bill-to, default bill-to, psra_default pay-from, default pay-from

Check Deleted for Changed

Join
Parameter
Value
Main Table
Find new/ changed/ deleted UN-MATCHED -Column
Main Table Alias
fncdr
Joins
Bring in LOV Values, lov, Left
Join Expressions
cast("fncdr"."geo_efftv_end_dt" as date) <> cast("lov"."efftv_end_dt" as date)
AND cast("fncdr"."geo_efftv_start_dt" as date) = cast("lov"."efftv_start_dt" as date)
AND "fncdr"."algn_struc_cd" = "lov"."algn_struc_cd"
AND "fncdr"."geo_id" = "lov"."geo_id"
AND "fncdr"."team_id" = "lov"."team_id", fncdr_Left_lov
Output Columns
fncdr.participant_id, participant_id, fncdr.algn_struc_cd, algn_struc_cd, fncdr.geo_efftv_start_dt, geo_efftv_start_dt, fncdr.geo_efftv_end_dt, geo_efftv_end_dt, fncdr.geo_id, geo_id, fncdr.team_id, team_id, fncdr.org id prefix, org id prefix, fncdr.default bill-to, default bill-to, fncdr.default pay-from, default pay-from, lov.participant_id, lov_participant_id, lov.algn_struc_cd, lov_algn_struc_cd, lov.geo_id, lov_geo_id, lov.core_temp_cd, lov_core_temp_cd, lov.split_pct, lov_split_pct, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov.org id prefix, lov_org id prefix, lov.default bill-to, lov_default bill-to, lov.default pay-from, lov_default pay-from

Check Deleted for Changed - Un-Matched

Filter
Parameter
Value
Filter Conditions
lov_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Check Deleted for Changed - Un-matched: Calculate

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
'N', Delete Indicator

Bring New/Changed and Deleted

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
Yes

Get Territory Level

Join
Parameter
Value
Main Table
Bring New/Changed and Deleted
Main Table Alias
bncd
Joins
Get Latest Geography: Filter Active, glgfa, Inner
Join Expressions
"bncd"."algn_struc_cd" = "glgfa"."algn_struc_cd"
AND "bncd"."geo_id" = "glgfa"."geo_id" , bncd_Inner_glgfa
Output Columns
bncd.participant_id, participant_id, bncd.algn_struc_cd, algn_struc_cd, bncd.geo_id, geo_id, bncd.team_id, team_id, bncd.efftv_start_dt, efftv_start_dt, bncd.efftv_end_dt, efftv_end_dt, bncd.delete indicator, delete indicator, bncd.org id prefix, org id prefix, bncd.default bill-to, default bill-to, bncd.default pay-from, default pay-from, glgfa.level_cd, level_cd

Get Territory Level - Filter Out X999

Filter
Parameter
Value
Filter Conditions
participant_id, Not, Ilike, X999
Combine Conditions
AND

Create Constants

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
'OPCO_END', provider id, CASE WHEN "level_cd" ilike '%terr%' THEN
"team_id"
ELSE
Concat("org id prefix","team_id")
END
, organization Id , 'N', primary rep Indicator, cast(getdate() as date), last_updated_dt, 'N', sales rep pay-from Indicator, '', sales rep universal customer n, 'N', sales rep bill-to indicator

Filter Out Not in Sales Roster

Join
Parameter
Value
Main Table
Create Constants
Main Table Alias
cc
Joins
Load - Sales Roster, sr, Left
Join Expressions
"cc"."participant_id" = "sr"."jj_ww_id", cc_Left_sr
Output Columns
cc.algn_struc_cd, algn_struc_cd, cc.participant_id, participant_id, cc.team_id, team_id, cc.efftv_start_dt, efftv_start_dt, cc.efftv_end_dt, efftv_end_dt, cc.delete indicator, delete indicator, cc.geo_id, geo_id, cc.org id prefix, org id prefix, cc.default bill-to, default bill-to, cc.default pay-from, default pay-from, cc.level_cd, level_cd, cc.provider id, provider id, cc.organization id , organization id, cc.primary rep indicator, primary rep indicator, cc.last_updated_dt, last_updated_dt, cc.sales rep pay-from indicator, sales rep pay-from indicator, cc.sales rep universal customer n, sales rep universal customer n, cc.sales rep bill-to indicator, sales rep bill-to indicator, sr.jj_ww_id, wwid

Filter Out Not In Sales Roster: Matched

Filter
Parameter
Value
Filter Conditions
wwid, Not, Null or blank
Combine Conditions
AND

ETH MDM21 Sales Rep Affiliation: Rename

Rename
Parameter
Value
Column Mapping
provider id, Provider ID, participant_id, World Wide ID, organization id, Organization ID, efftv_start_dt, Affiliation Effective Date, efftv_end_dt, Affiliation End Date, primary rep indicator, Primary Rep Indicator, delete indicator, Delete Indicator, last_updated_dt, Last Update Date, sales rep bill-to indicator, Sales Rep Bill-to indicator, default bill-to, Bill-To Universal Customer Number, sales rep pay-from indicator, Sales Rep Pay-From Indicator, default pay-from, Pay-From Universal Customer Number, sales rep universal customer n, Sales Rep Universal Customer Number

Distinct 0

Distinct
Parameter
Value
Columns
provider id, world wide id, organization id, affiliation effective date, affiliation end date, primary rep indicator, delete indicator, last update date, sales rep bill-to indicator, bill-to universal customer number, sales rep pay-from indicator, pay-from universal customer number, sales rep universal customer number

Calculator 0

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
to_char("affiliation effective date",'YYYYMMDD'), affiliation effective date, to_char("affiliation end date",'YYYYMMDD'), affiliation end date, to_char("last update date",'YYYYMMDD'), last update date

Daily - MDM21 Sales Rep Affiliation

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
outbound_dest_eth_mdm21_sales_rep_affiliation
Fix Data Type Mismatches
No
Column Mapping
provider id, provider id, world wide id, world wide id, organization id, organization id, affiliation effective date, affiliation effective date, affiliation end date, affiliation end date, primary rep indicator, primary rep indicator, delete indicator, delete indicator, last update date, last update date, sales rep bill-to indicator, sales rep bill-to indicator, bill-to universal customer number, bill-to universal customer number, sales rep pay-from indicator, sales rep pay-from indicator, pay-from universal customer number, pay-from universal customer number, sales rep universal customer number, sales rep universal customer number
Truncate
Truncate
Automatic Compression
No

Distinct Participant ID

Distinct
Parameter
Value
Columns
participant_id

Post to MDM Participant ID in Sales Rep Affil

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
mdm part id in sales rep affil
Fix Data Type Mismatches
No
Column Mapping
participant_id, participant_id
Truncate
Truncate
Automatic Compression
No

Filter Out Not In Sales Roster: Un-Matched

Filter
Parameter
Value
Filter Conditions
wwid, Is, Null or blank
Combine Conditions
AND

Check Deleted for Changed - Matched

Filter
Parameter
Value
Filter Conditions
lov_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Get Ended Territories

Join
Parameter
Value
Main Table
Check Deleted for Changed - Matched
Main Table Alias
cdfc
Joins
MDM Prior Org, mdmpo, Inner
Join Expressions
"cdfc"."algn_struc_cd" = "mdmpo"."algn_struc_cd"
AND "cdfc"."geo_id" = "mdmpo"."geo_id" , cdfc_Inner_mdmpo
Output Columns
cdfc.participant_id, participant_id, cdfc.algn_struc_cd, algn_struc_cd, cdfc.geo_efftv_start_dt, geo_efftv_start_dt, cdfc.geo_efftv_end_dt, geo_efftv_end_dt, cdfc.geo_id, geo_id, cdfc.team_id, team_id, cdfc.org id prefix, org id prefix, cdfc.default bill-to, default bill-to, cdfc.default pay-from, default pay-from

Check Held Territories

Join
Parameter
Value
Main Table
Hold New Territories - Un-Matched
Main Table Alias
hntun
Joins
Get Ended Territories, get, Inner
Join Expressions
"hntun"."algn_struc_cd" = "get"."algn_struc_cd"
AND "hntun"."geo_id" = "get"."geo_id"
AND "hntun"."team_id" = "get"."team_id"
AND Cast("hntun"."efftv_start_dt" as date) = Cast("get"."geo_efftv_start_dt" as date), hntun_Inner_get
Output Columns
hntun.algn_struc_cd, algn_struc_cd, hntun.participant_id, participant_id, hntun.efftv_start_dt, efftv_start_dt, hntun.efftv_end_dt, efftv_end_dt, hntun.geo_id, geo_id, hntun.team_id, team_id, hntun.org id prefix, org id prefix, hntun.default bill-to, default bill-to, hntun.default pay-from, default pay-from, hntun.split_pct, split_pct, hntun.core_temp_cd, core_temp_cd

Check held Territories: Calculate

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
'N', Delete Indicator

Find new/ changed/ deleted UN-MATCHED LEFT

Filter
Parameter
Value
Filter Conditions
psra_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Find new/ changed/ deleted UN-MATCHED LEFT-Column

Rename
Parameter
Value
Column Mapping
lov_participant_id, participant_id, lov_algn_struc_cd, algn_struc_cd, lov_geo_id, geo_id, lov_team_id, team_id, lov_core_temp_cd, core_temp_cd, lov_split_pct, split_pct, efftv_start_dt1, efftv_start_dt1, efftv_end_dt1, efftv_end_dt1, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov_org id prefix, org id prefix, lov_default bill-to, default bill-to, lov_default pay-from, default pay-from

Identify Future Dated Records

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
/*CASE WHEN Cast("efftv_start_dt" as date) > CAST('01-15-2020' as DATE)
OR Cast("efftv_end_dt" as date) >= CAST('01-15-2020' as DATE)
AND Cast("efftv_end_dt" as date) != CAST('12-31-9999' as DATE) THEN
'Yes'
ELSE
'No'
END*/
CASE WHEN Cast("efftv_start_dt" as date) > Cast(getdate() as date)
OR (Cast("efftv_end_dt" as date) >= Cast(getdate() as date)
AND Cast("efftv_end_dt" as date) != Cast('12-31-9999' as date)) THEN
'Yes'
ELSE
'No'
END
, Is_Future_Dated_Flag, Cast(getdate() as date), Process Date

Identify Future Dated Records: Active

SQL
Parameter
Value
SQL Query
SELECT * FROM
(SELECT
"algn_struc_cd",
"participant_id",
"efftv_start_dt1",
"efftv_end_dt1",
"efftv_start_dt",
"efftv_end_dt",
'N' as "Delete Indicator",
"geo_id",
"team_id",
"org id prefix",
"default bill-to",
"default pay-from",
"split_pct",
"core_temp_cd",
CASE WHEN Cast("efftv_start_dt" as date) > Cast(getdate() as date)
OR (Cast("efftv_end_dt" as date) >= Cast(getdate() as date)
AND Cast("efftv_end_dt" as date) != Cast('12-31-9999' as date)) THEN
'Yes'
ELSE
'No'
END
AS "Is_Future_Dated_Flag",
Cast(getdate() as date)
AS "Process Date"
FROM ($T{Identify Future Dated Records})) A
WHERE
A."Is_Future_Dated_Flag" = 'No'

Hold New Territories

Join
Parameter
Value
Main Table
Identify Future Dated Records: Active
Main Table Alias
ifdr
Joins
MDM Prior Org, mdmpo, Left
Join Expressions
"ifdr"."algn_struc_cd" = "mdmpo"."algn_struc_cd"
AND "ifdr"."team_id" = "mdmpo"."geo_id" , ifdr_Left_mdmpo
Output Columns
ifdr.algn_struc_cd, algn_struc_cd, ifdr.participant_id, participant_id, ifdr.efftv_start_dt1, efftv_start_dt1, ifdr.efftv_end_dt1, efftv_end_dt1, ifdr.efftv_start_dt, efftv_start_dt, ifdr.efftv_end_dt, efftv_end_dt, ifdr.delete indicator, delete indicator, ifdr.geo_id, geo_id, ifdr.team_id, team_id, ifdr.org id prefix, org id prefix, ifdr.default bill-to, default bill-to, ifdr.default pay-from, default pay-from, ifdr.split_pct, split_pct, ifdr.core_temp_cd, core_temp_cd, ifdr.is_future_dated_flag, is_future_dated_flag, ifdr.process date, process date, mdmpo.algn_struc_cd, mdmpo_algn_struc_cd

Hold New Territories - Un-Matched

Filter
Parameter
Value
Filter Conditions
mdmpo_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Hold New Territories - Matched

Filter
Parameter
Value
Filter Conditions
mdmpo_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Identify Future Dated Records - Filter Yes

Filter
Parameter
Value
Filter Conditions
is_future_dated_flag, Is, Equal to, Yes
Combine Conditions
AND

Param: Geo Hire Cal Dates Filter Division

Filter
Parameter
Value
Filter Conditions
level_cd, Is, Ilike, Division
Combine Conditions
AND

Part-Geo: Expand by Upper Division

Join
Parameter
Value
Main Table
Part-Geo: Format Dates Filter Division
Main Table Alias
pgfd
Joins
Param: Geo Hire Cal Dates Filter Division, ghfd, Left
Join Expressions
"pgfd"."algn_struc_cd" = "ghfd"."upper_algn_struc_cd"
AND cast("pgfd"."efftv_start_dt" as date) <= cast("ghfd"."efftv_end_dt" as date)
AND cast("pgfd"."efftv_end_dt" as date) >= cast("ghfd"."efftv_start_dt" as date), pgfd_Left_ghfd
Output Columns
pgfd.participant_id, participant_id, pgfd.algn_struc_cd, algn_struc_cd, pgfd.geo_id, geo_id, pgfd.core_temp_cd, core_temp_cd, pgfd.split_pct, split_pct, pgfd.last_updated_dt, last_updated_dt, pgfd.efftv_start_dt, efftv_start_dt, pgfd.efftv_end_dt, efftv_end_dt, ghfd.lower_algn_struc_cd, lower_algn_struc_cd, ghfd.efftv_start_dt, new_efftv_start_dt, ghfd.efftv_end_dt, new_efftv_end_dt

Part-Geo: Upper Div Filter Nulls

Filter
Parameter
Value
Filter Conditions
lower_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Input: Team-Geo Association

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_customerteam_geo_assoc
Column Names
algn_struc_cd, geo_id, team_id, split_pct, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Team-Geo: Rename Columns

Rename
Parameter
Value
Column Mapping
last_updated_dt, last_updated, algn_struc_cd, algn_struc_cd, geo_id, geo_id, team_id, team_id, split_pct, split_pct, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt

Team-Geo: Start Date

Join
Parameter
Value
Main Table
Team-Geo: Rename Columns
Main Table Alias
tga
Joins
Input: Fiscal Calendar 2, fc, Left
Join Expressions
cast("tga"."efftv_start_dt" as date) = cast("fc"."efftv_start_dt" as date), tga_Left_fc
Output Columns
tga.algn_struc_cd, algn_struc_cd, tga.geo_id, geo_id, tga.team_id, team_id, tga.split_pct, split_pct, tga.efftv_start_dt, tga_efftv_start_dt, tga.efftv_end_dt, tga_efftv_end_dt, tga.last_updated, last_updated, fc.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Team-Geo: End Date

Join
Parameter
Value
Main Table
Team-Geo: Start Date
Main Table Alias
tgsd
Joins
Input: Fiscal Calendar 2, fc, Left
Join Expressions
cast("tgsd"."tga_efftv_end_dt" as date) = cast("fc"."efftv_end_dt" as date), tgsd_Left_fc
Output Columns
tgsd.algn_struc_cd, algn_struc_cd, tgsd.geo_id, geo_id, tgsd.team_id, team_id, tgsd.split_pct, split_pct, tgsd.last_updated, last_updated, tgsd.fiscal_year_wk_start_dt, tg_efftv_start_dt, fc.fiscal_year_wk_end_dt, tg_efftv_end_dt

Team-Geo: Adjust Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "tg_efftv_start_dt" is null THEN
to_date('01/01/1900','MM/DD/YYYY')
ELSE
"tg_efftv_start_dt"
END, tg_efftv_start_dt, CASE WHEN "tg_efftv_end_dt" is null THEN
to_date('12/31/9999','MM/DD/YYYY')
ELSE
"tg_efftv_end_dt"
end, tg_efftv_end_dt

Input: Geographies

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_geo_master_outbound
Column Names
algn_struc_cd, geo_id, geo_nm, level_cd, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Geographies - Format Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
Cast("efftv_start_dt" as date), efftv_start_dt, Cast("efftv_end_dt" as date), efftv_end_dt

Get Latest Geography

Join
Parameter
Value
Main Table
Geographies - Format Dates
Main Table Alias
g
Joins
Aggregate Geographies, ag, Inner
Join Expressions
"g"."algn_struc_cd" = "ag"."algn_struc_cd"
AND "g"."geo_id" = "ag"."geo_id"
AND cast("g"."efftv_start_dt" as date) = cast("ag"."max_efftv_start_dt" as date), g_Inner_ag
Output Columns
g.algn_struc_cd, algn_struc_cd, g.geo_id, geo_id, g.geo_nm, geo_nm, g.level_cd, level_cd, g.efftv_start_dt, efftv_start_dt, g.efftv_end_dt, efftv_end_dt, g.last_updated_dt, last_updated

Get Latest Geography: Filter Active

SQL
Parameter
Value
SQL Query
SELECT
"algn_struc_cd",
"geo_id",
"geo_nm",
"level_cd",
"efftv_start_dt",
"efftv_end_dt",
"last_updated"
FROM ($T{Get Latest Geography})
WHERE
Cast("efftv_start_dt" as date) <= Cast(getdate() as date)
AND Cast("efftv_end_dt" as date) >= Cast(getdate() as date)
AND "geo_nm" NOT ilike 'Nation'

Aggregate Geographies

Aggregate
Parameter
Value
Groupings
algn_struc_cd, geo_id
Aggregations
efftv_start_dt, Max

MDM LOV Parameters

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_mdm_lov_param
Column Names
alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date
Trim Columns
No

Participant-Geo Association

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_participant_geo_assoc
Column Names
algn_struc_cd, participant_id, geo_id, core_temp_cd, split_pct, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Part-Geo: Rename Columns

Rename
Parameter
Value
Column Mapping
algn_struc_cd, algn_struc_cd, participant_id, participant_id, geo_id, geo_id, core_temp_cd, core_temp_cd, split_pct, split_pct, efftv_start_dt, geo_efftv_start_dt, efftv_end_dt, geo_efftv_end_dt, last_updated_dt, last_updated

Part-Geo: Get Core Only

Filter
Parameter
Value
Filter Conditions
core_temp_cd, Is, Ilike, C
Combine Conditions
AND

Part-Geo: Start Date

Join
Parameter
Value
Main Table
Part-Geo: Get Core Only
Main Table Alias
pga
Joins
Input: Fiscal Calendar, fc, Left
Join Expressions
cast("pga"."geo_efftv_start_dt" as date) = cast("fc"."efftv_start_dt" as date), pga_Left_fc
Output Columns
pga.algn_struc_cd, algn_struc_cd, pga.participant_id, participant_id, pga.geo_id, geo_id, pga.core_temp_cd, core_temp_cd, pga.split_pct, split_pct, pga.last_updated, last_updated, pga.geo_efftv_start_dt, geo_efftv_start_dt, pga.geo_efftv_end_dt, geo_efftv_end_dt, fc.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Part-Geo: End Date

Join
Parameter
Value
Main Table
Part-Geo: Start Date
Main Table Alias
pgsd
Joins
Input: Fiscal Calendar, fc, Left
Join Expressions
cast("pgsd"."geo_efftv_end_dt" as date) = cast("fc"."efftv_end_dt" as date), pgsd_Left_fc
Output Columns
pgsd.algn_struc_cd, algn_struc_cd, pgsd.participant_id, participant_id, pgsd.geo_id, geo_id, pgsd.core_temp_cd, core_temp_cd, pgsd.split_pct, split_pct, pgsd.last_updated, last_updated_dt, pgsd.fiscal_year_wk_start_dt, pg_efftv_start_dt, fc.fiscal_year_wk_end_dt, pg_efftv_end_dt

Part-Geo: Calc Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "pg_efftv_start_dt" IS NULL THEN
to_date('01/01/1900' ,'MM/DD/YYYY')
ELSE
"pg_efftv_start_dt"
END

, efftv_start_dt, CASE WHEN "pg_efftv_end_dt" IS NULL THEN
to_date('12/31/9999' ,'MM/DD/YYYY')
ELSE
"pg_efftv_end_dt"
END

, efftv_end_dt, cast("last_updated_dt" as date), last_updated_dt

Part-Geo: Filter Territory

Filter
Parameter
Value
Filter Conditions
geo_id, Not, Like, ____
Combine Conditions
AND

Part-Geo: Filter Terr

Filter
Parameter
Value
Filter Conditions
geo_id, Is, Like, ____
Combine Conditions
AND

Part-Geo: Format Dates Filter Division

Filter
Parameter
Value
Filter Conditions
geo_id, Is, Like, __
Combine Conditions
AND

Input: Fiscal Calendar

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_fiscal_calendar
Column Names
efftv_start_dt, efftv_end_dt, datamonth, fiscal_year_month, fiscal_year_wk_start_dt, fiscal_year_wk_end_dt
Trim Columns
No

Input: Fiscal Calendar 2

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_fiscal_calendar
Column Names
efftv_start_dt, efftv_end_dt, datamonth, fiscal_year_month, fiscal_year_wk_start_dt, fiscal_year_wk_end_dt
Trim Columns
No

Param: Alignment Structure

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_alignment_structures
Column Names
algn_struc_cd, algn_struc_nm, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Input: MDM Prior Sale Rep Affiliation

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
mdm_prior_sale_rep_affiliation
Column Names
participant_id, algn_struc_cd, geo_efftv_start_dt, geo_efftv_end_dt, team_id, geo_id, org id prefix, default bill-to, default pay-from
Trim Columns
No

MDM Prior Org

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_mdm21_prior_org
Column Names
geo_id, algn_struc_cd
Trim Columns
No

MDM Prior Org (2)

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_mdm21_prior_org
Column Names
geo_id, algn_struc_cd
Trim Columns
No

Load - Sales Roster

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_sales_roster
Column Names
reg_no, reg_nm, div_no, div_nm, terr_no, terr_nm, fran_cd, posn_cd, posn_nm, pos_refnc_cd, area_id, first_nm, last_nm, vmail_no, addr_ln_1_txt, apt_no, addr_ln_2_txt, city_nm, stt_cd, postl_cd, tel_no, ssan_no, pgr_1_no, pin_1_no, pgr_2_no, pin_2_no, fax_no, cell_tel_no, sfa_pda_ovrrd, sex_cd, stat_cd, email_addr_text, cstctr_no, bday_dt, hire_dt, spse_nm, spse_ssn_no, chldn_txt, pg_no, actv_ind, jj_ww_id, dendrite_id
Trim Columns
No

Job: Daily-MDM21 Sales Rep Affiliation - Post Prior Sales Rep Affil

Load: stg_mdm21_prior_sales rep affiliation

Table Input
Parameter
Value
Schema
${Schema_2}
Table Name
stg_mdm_prior_sale_rep_affiliation
Column Names
participant_id, algn_struc_cd, geo_efftv_start_dt, geo_efftv_end_dt, team_id, geo_id, org id prefix, default bill-to, default pay-from
Trim Columns
No

Post to - MDM21 Prior Sales Rep Affil

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
mdm_prior_sale_rep_affiliation
Fix Data Type Mismatches
Yes
Column Mapping
participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, team_id, team_id, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from
Truncate
Truncate
Automatic Compression
No

Job: Archive Outbound Global Function

Add TimeStamp & Get File,Table Information from context of the job run

Python Script
Parameter
Value
Script
import datetime

x = datetime.datetime.now()
x=str(x).split('.')

x=x[0]
print('time_stamp :'+str(x))
print('Archive_temp_current_table :'+str(Archive_temp_current_table))
print('Archive_temp_current_file :'+str(Archive_temp_current_file))

Archive_temp_current_file=Archive_temp_current_file+'_'+x+'__'
print('Archive_temp_current_file upt :'+str(Archive_temp_current_file))
context.updateVariable('Archive_temp_current_file', str(Archive_temp_current_file))
print('Archive_type :'+Archive_type)


#dynamically change path for Archive
if Archive_type=='ONEMD':
S3_temp_Archive_Location=S3_ONEMD_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))
elif Archive_type=='MDM21':
S3_temp_Archive_Location=S3_MDM21_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))
else:
S3_temp_Archive_Location=S3_EUSS_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))




Interpreter
Jython

If ONEMD

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, ONEMD
Combine Conditions
And

S3 Archive(ONEMD)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_ONEMD_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

If MDM21

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, MDM21
Combine Conditions
And

S3 Archive(MDM21)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_MDM21_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

If EUSS

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, EUSS
Combine Conditions
And

S3 Archive(EUSS)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_EUSS_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None